package test.testcase.dao;

import org.json.JSONArray;
import test.testcase.TestJob;
import test.utils.GetValue;
import test.utils.JsonResult;
import test.utils.Mysql;

public class JobDao {
	
	public static JsonResult addProjectJob(String projectid) {
		return Mysql.excute("insert into job_excute_log values(null,null,"+projectid+",null,0,now(),null)");
	}
	
	public static JsonResult addAnPenddingJob(String timeId) {
		return Mysql.excute("insert into job_excute_log select null,jobid,projectid,null,-1,now(),null from timer where id="+timeId);
	}
	
	public static JsonResult addAnPenddingJobWithJobId(String jobid) {
		return Mysql.excute("insert into job_excute_log values(null,"+jobid+",null,null,-1,now(),null)");
	}
	
	public static JsonResult delJobLog(String id) {
		return Mysql.excute("delete from job_excute_log where id="+id);
	}
	
	public static void startJob(TestJob job) {
		if (job != null && job.getId() != null) {
			Mysql.excute("update job_excute_log set status=1, starttime='"+GetValue.nowDateTimeStr("yyyy-MM-dd HH:mm:ss")+"' where id="+job.getExcuteid());
		}
	}
	
	public static void addAnExcuteJob(TestJob job) {
		if (job != null) {
			if (!"".equals(job.getExcuteid()) && job.getExcuteid() != null) {
				Mysql.excute("update job_excute_log set status=0 where id="+job.getExcuteid());
			}else {
				Mysql.excute("insert into job_excute_log values(null,"+job.getId()+",null,null,0,now(),now())");
			}
		}
	}
	
	public static void addAnExcuteProjectJob(TestJob job) {
		if (job != null) {
			if (!"".equals(job.getExcuteid()) && job.getExcuteid() != null) {
				Mysql.excute("update job_excute_log set status=0 where id="+job.getExcuteid());
			}else {
				Mysql.excute("insert into job_excute_log values(null,null,"+job.getProjectid()+",0,now(),now())");
			}
		}
	}
	
	public static void endJob(TestJob job) {
		if (job != null) {
			Mysql.excute("update job_excute_log set status='2', endtime='"+GetValue.nowDateTimeStr("yyyy-MM-dd HH:mm:ss")+"' where id="+job.getExcuteid()+" and status=1");
		}
	}
	
	public static void endJobWithErr(TestJob job) {
		if (job != null) {
			Mysql.excute("update job_excute_log set status='-2', endtime='"+GetValue.nowDateTimeStr("yyyy-MM-dd HH:mm:ss")+"' where id="+job.getExcuteid()+" and status=1");
		}
	}
	
	public static TestJob getJobByExcuteId(String excuteid) {
		JsonResult result = Mysql.select("SELECT a.jobid,a.buildnumber,a.id as excuteid,b.*,a.projectid as projectid1 FROM job_excute_log a left join job b on a.jobid=b.id WHERE a.id="+excuteid);
		JSONArray array = result.read("data").toJsonArray();
		TestJob testJob = new TestJob();
		if (array != null) {
			testJob.setCreatetime(result.read("data[0].createtime").toString());
			testJob.setUserid(result.read("data[0].userid").toString());
			testJob.setUserid(result.read("data[0].userid").toString());
			testJob.setId(result.read("data[0].id").toString());
			testJob.setTitle(result.read("data[0].title").toString());
			testJob.setStatus(result.read("data[0].status").toString());
			testJob.setDesc(result.read("data[0].desc").toString());
			testJob.setUpdatetime(result.read("data[0].updatetime").toString()); 
			testJob.setExcuteid(result.read("data[0].excuteid").toString());
			testJob.setProjectid(result.read("data[0].projectid").toString());
			if ("".equals(testJob.getProjectid())) {
				testJob.setProjectid(result.read("data[0].projectid1").toString());
			}
			testJob.setBuildNumber(result.read("data[0].buildnumber").toString());
		}
		return testJob;
	}
	
	public static void setJobBuildNumber(TestJob job, String buildNumber) {
		Mysql.excute("update job_excute_log set buildnumber="+buildNumber+" where id="+job.getExcuteid());
	}
	
	public static JsonResult getJobCases(String jobid, int start, int end, String where) {
		JsonResult result = Mysql.select("SELECT * FROM `job_case` a LEFT JOIN `testcase` b ON a.`testcaseid`=b.`id` left join project_typetag d on b.typetag=d.id WHERE jobid="+jobid+ where+ " limit "+start+","+end);
		return result;
	}
	
	public static void addJobCase(String jobid, String caseId) {
		Mysql.excute("replace into job_case values("+jobid+", "+caseId+")");
	}
	
	public static void addJobCases(String sql) {
		Mysql.excute("replace into job_case "+sql);
	}

	public static void removeCaseFromJob(String jobid, String testcaseid) {
		Mysql.excute("delete from job_case where jobid="+jobid+" and testcaseid in ("+testcaseid+")");
	}
	
	public static void removeCasesFromJob(String jobid, String sql) {
		Mysql.excute("delete from job_case where jobid="+jobid+" and testcaseid in ("+sql+")");
	}

	public static JsonResult getJobList(String projectid) {
		JsonResult result = Mysql.select("SELECT a.*,b.*,c.* FROM job a LEFT JOIN USER b ON a.`userid`=b.`id` LEFT JOIN project c ON a.`projectid`=c.`id` where a.projectid="+projectid);
		return result;
	}
	
	public static JsonResult getJobListByprojectid(String projectid) {
		JsonResult result = Mysql.select("SELECT a.*,b.*,c.* FROM job a LEFT JOIN USER b ON a.`userid`=b.`id` LEFT JOIN project c ON a.`projectid`=c.`id` where a.projectid="+projectid);
		return result;
	}
	
	public static JsonResult getJobExcuteLogList(String projectid, int start, int end) {
		JsonResult result = Mysql.select("SELECT DISTINCT \r\n" + 
				"  b.id AS excuteid,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN b.projectid IS NULL \r\n" + 
				"      THEN b.projectid1 \r\n" + 
				"      ELSE b.projectid\r\n" + 
				"    END\r\n" + 
				"  ) AS projectid,\r\n" + 
				"  b.starttime,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN b.projectid IS NULL \r\n" + 
				"      THEN b.projectname \r\n" + 
				"      ELSE b.projectname1 \r\n" + 
				"    END\r\n" + 
				"  ) AS projectname,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN b.projectid IS NOT NULL \r\n" + 
				"      THEN \"全部用例\" \r\n" + 
				"      ELSE b.title \r\n" + 
				"    END\r\n" + 
				"  ) AS title,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      b.status \r\n" + 
				"      WHEN 0 \r\n" + 
				"      THEN '待执行' \r\n" + 
				"      WHEN 1 \r\n" + 
				"      THEN '正在执行' \r\n" + 
				"      WHEN 2 \r\n" + 
				"      THEN '执行完成' \r\n" + 
				"      WHEN -2 \r\n" + 
				"      THEN '异常' \r\n" + 
				"      ELSE '准备' \r\n" + 
				"    END\r\n" + 
				"  ) AS STATUS,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN buildnumber IS NULL \r\n" + 
				"      THEN 'no' \r\n" + 
				"      ELSE buildnumber \r\n" + 
				"    END\r\n" + 
				"  ) AS buildnumber \r\n" + 
				"FROM\r\n" + 
				"  (SELECT \r\n" + 
				"    a.*,\r\n" + 
				"    c.`title`,\r\n" + 
				"    d.`projectname`,\r\n" + 
				"    e.projectname AS projectname1,\r\n" + 
				"    c.projectid AS projectid1\r\n" + 
				"  FROM\r\n" + 
				"    `job_excute_log` a \r\n" + 
				"    LEFT JOIN job c \r\n" + 
				"      ON a.`jobid` = c.`id` \r\n" + 
				"    LEFT JOIN project d \r\n" + 
				"      ON c.`projectid` = d.`id` \r\n" + 
				"    LEFT JOIN project e \r\n" + 
				"      ON a.projectid = e.id \r\n" + 
				"  ) b \r\n" + 
				"WHERE b.projectid="+projectid+"\r\n" + 
				"  OR b.jobid IN \r\n" + 
				"  (SELECT \r\n" + 
				"    id \r\n" + 
				"  FROM\r\n" + 
				"    job \r\n" + 
				"  WHERE projectid = "+projectid+") ORDER BY b.starttime DESC limit "+start+","+end);
		return result;
	}
	
	public static JsonResult getJobExcuteLogListByStatus(String projectid, int start, int end, String status) {
		JsonResult result = Mysql.select("SELECT DISTINCT \r\n" + 
				"  b.id AS excuteid,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN b.projectid IS NULL \r\n" + 
				"      THEN b.projectid1 \r\n" + 
				"      ELSE b.projectid\r\n" + 
				"    END\r\n" + 
				"  ) AS projectid,\r\n" + 
				"  b.starttime,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN b.projectid IS NULL \r\n" + 
				"      THEN b.projectname \r\n" + 
				"      ELSE b.projectname1 \r\n" + 
				"    END\r\n" + 
				"  ) AS projectname,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN b.projectid IS NOT NULL \r\n" + 
				"      THEN \"全部用例\" \r\n" + 
				"      ELSE b.title \r\n" + 
				"    END\r\n" + 
				"  ) AS title,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      b.status \r\n" + 
				"      WHEN 0 \r\n" + 
				"      THEN '待执行' \r\n" + 
				"      WHEN 1 \r\n" + 
				"      THEN '正在执行' \r\n" + 
				"      WHEN 2 \r\n" + 
				"      THEN '执行完成' \r\n" + 
				"      WHEN -2 \r\n" + 
				"      THEN '异常' \r\n" + 
				"      ELSE '准备' \r\n" + 
				"    END\r\n" + 
				"  ) AS STATUS,\r\n" + 
				"  (\r\n" + 
				"    CASE\r\n" + 
				"      WHEN buildnumber IS NULL \r\n" + 
				"      THEN 'no' \r\n" + 
				"      ELSE buildnumber \r\n" + 
				"    END\r\n" + 
				"  ) AS buildnumber \r\n" + 
				"FROM\r\n" + 
				"  (SELECT \r\n" + 
				"    a.*,\r\n" + 
				"    c.`title`,\r\n" + 
				"    d.`projectname`,\r\n" + 
				"    e.projectname AS projectname1,\r\n" + 
				"    c.projectid AS projectid1\r\n" + 
				"  FROM\r\n" + 
				"    `job_excute_log` a \r\n" + 
				"    LEFT JOIN job c \r\n" + 
				"      ON a.`jobid` = c.`id` \r\n" + 
				"    LEFT JOIN project d \r\n" + 
				"      ON c.`projectid` = d.`id` \r\n" + 
				"    LEFT JOIN project e \r\n" + 
				"      ON a.projectid = e.id \r\n" + 
				"  ) b \r\n" + 
				"WHERE b.status = '"+status+"' and (b.projectid="+projectid+"\r\n" + 
				"  OR b.jobid IN \r\n" + 
				"  (SELECT \r\n" + 
				"    id \r\n" + 
				"  FROM\r\n" + 
				"    job \r\n" + 
				"  WHERE projectid = "+projectid+")) ORDER BY b.starttime DESC limit "+start+","+end);
		return result;
	}
	
	public static JsonResult addJob(String title, String desc, String userid, String projectid) {
		Mysql.excute("insert into job (title,`desc`,userid,projectid,`status`,createtime,updatetime) values('"+title+"','"+desc+"',"+userid+","+projectid+",1,now(),now())");
		JsonResult result = Mysql.select("select id from job order by id desc limit 1");
		String jobid = result.read("data[0].id").toString();
		return Mysql.excute("insert into job_excute_log (jobid) values("+jobid+")");
	}
	
	public static JsonResult del(String id) {
		Mysql.excute("delete from job_case where jobid="+id);
		Mysql.excute("delete from `job_excute_log` where jobid="+id);
		return Mysql.excute("delete from job where id="+id);
	}
	
	public static JsonResult getAnExecutingJob(String projectid) {
		JsonResult result = Mysql.select("SELECT a.jobid,a.id as excuteid,b.* FROM job_excute_log a left join job b on a.jobid=b.id left join project c on b.projectid=c.id WHERE a.STATUS=1 and c.id="+projectid);
		return result;
	}
	
	public static JsonResult getAnExecutingJob() {
		JsonResult result = Mysql.select("SELECT a.jobid,a.id as excuteid,b.*,a.buildnumber FROM job_excute_log a left join job b on a.jobid=b.id left join project c on b.projectid=c.id WHERE a.STATUS=1");
		return result;
	}
	
	public static JsonResult getAnExecutableJob(String projectid) {
		JsonResult result = Mysql.select("SELECT a.jobid,a.id as excuteid,b.* FROM job_excute_log a left join job b on a.jobid=b.id WHERE a.STATUS=0 and (a.`projectid`="+projectid+" OR b.`projectid`="+projectid+") limit 1");
		return result;
	}
	
	public static JsonResult getAnExecutableJob() {
		JsonResult result = Mysql.select("SELECT a.jobid,a.id as excuteid,b.* FROM job_excute_log a left join job b on a.jobid=b.id left join project c on b.projectid=c.id WHERE a.STATUS=0 limit 1");
		return result;
	}
	
	public static JsonResult getAnPenddingJob() {
		JsonResult result = Mysql.select("SELECT a.jobid,a.id as excuteid,b.`projectid`,b.*,a.buildnumber FROM job_excute_log a left join job b on a.jobid=b.id left join project c on b.projectid=c.id WHERE a.STATUS=-1 limit 1");
		return result;
	}
	
	public static JsonResult getJob(String jobid) {
		JsonResult result = Mysql.select("SELECT a.jobid,a.id as excuteid,b.* FROM job_excute_log a left join job b on a.jobid=b.id WHERE b.id="+jobid);
		return result;
	}
	
}
